using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;

namespace DIS_DAO.DAO
{
    /// <summary>
    /// Data provider class, which support some functions to access database
    /// </summary>
	public class AbstractDAO
	{
		protected String sql;
		protected SqlConnection objConnection;
		protected SqlCommand objCommand;
		protected SqlDataAdapter objDataAdapter;
		protected SqlDataReader objDataReader;

		/// <summary>
		/// Init object sql connection
		/// </summary>
		/// <param name="strPath"></param>
		public AbstractDAO(String strPath)
		{
			try
			{
				string objConnectionString = strPath;

				objConnection = new SqlConnection(objConnectionString);
			}
			catch (System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
		}
		/// <summary>
		/// Add parameter for sql query
		/// </summary>
		/// <param name="Name"></param> 
		/// <param name="Type"></param>
		/// <param name="Size"></param>
		/// <param name="Value"></param>
		public void AddParameter(string Name, SqlDbType Type, int Size, object Value)
		{
			try
			{
				objCommand.Parameters.Add(Name, Type, Size).Value = Value;
			}
			catch (System.Exception OleDbExceptionErr)
			{
				throw new System.Exception(OleDbExceptionErr.Message,
				OleDbExceptionErr.InnerException);
			}
		}
		/// <summary>
		/// Add parameter, get returned value when executing a store procedure
		/// </summary>
		/// <param name="Name"></param>
		/// <param name="Type"></param>
		/// <param name="Size"></param>
		/// <param name="Value"></param>
		public void AddParameterWithDirection(string Name, SqlDbType Type, int Size)
		{
			try
			{
				SqlParameter param = objCommand.CreateParameter();
				param.ParameterName = Name;
				param.Direction = ParameterDirection.Output;
				param.SqlDbType = Type;
				param.Size = Size;
				objCommand.Parameters.Add(param);
			}
			catch (System.Exception OleDbExceptionErr)
			{
				throw new System.Exception(OleDbExceptionErr.Message,
				OleDbExceptionErr.InnerException);
			}
		}

        /// <summary>
        /// Get Parameter value
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
		public object GetParamValue(string Name)
		{
			return objCommand.Parameters[Name].Value;
		}

		/// <summary>
		/// Open connection
		/// </summary>
		public void OpenConnection()
		{
			try
			{
				objConnection.Open();
			}
			catch (SqlException ExceptionErr)
			{
				throw new Exception(ExceptionErr.Message, ExceptionErr.InnerException);
			}
			catch (InvalidOperationException InvalidOperationExceptionErr)
			{
				throw new Exception(InvalidOperationExceptionErr.Message, InvalidOperationExceptionErr.InnerException);
			}
		}
		/// <summary>
		/// Close connection
		/// </summary>
		public void CloseConnection()
		{
			objConnection.Close();
		}
		/// <summary>
		/// Init sql command
		/// </summary>
		public void InitializeCommand()
		{
			if (objCommand == null)
			{
				try
				{
					objCommand = new SqlCommand(sql, objConnection);
					//See if this is a stored procedure
					if (!sql.ToUpper().StartsWith("SELECT ")
						&& !sql.ToUpper().StartsWith("INSERT ")
						&& !sql.ToUpper().StartsWith("UPDATE ")
						&& !sql.ToUpper().StartsWith("DELETE "))
					{
						objCommand.CommandType = CommandType.StoredProcedure;
					}
				}
				catch (System.Exception e)
				{
					throw new Exception(e.Message, e.InnerException);
				}
			}
		}

        // Init data adapter
		public void InitializeDataAdapter()
		{
			try
			{
				objDataAdapter = new SqlDataAdapter();
				objDataAdapter.SelectCommand = objCommand;//lua chon cau lenh
			}
			catch (System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
		}

		/// <summary>
		/// Fill data to dataset
		/// </summary>
		/// <param name="daoDataSet"></param>
		/// <param name="TableName"></param>
		public void FillDataSet(ref DataSet daoDataSet, String TableName)
		{
			try
			{
				InitializeCommand();
				InitializeDataAdapter();
				objDataAdapter.Fill(daoDataSet, TableName);
			}
			catch (System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
		}

        /// <summary>
        /// Same as GC
        /// </summary>
		public void CleanUpObject()
		{
			objCommand.Dispose();
			objCommand = null;
			objDataAdapter.Dispose();
			objDataAdapter = null;
		}

		/// <summary>
		/// Fill data to datatable
		/// </summary>
		/// <param name="daoDataSet"></param>
		/// <param name="TableName"></param>
		public void FillDataTable(ref DataTable oDataTable)
		{
			try
			{
				InitializeCommand();
				InitializeDataAdapter();
				objDataAdapter.Fill(oDataTable);
			}
			catch (System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
			finally
			{
				objCommand.Dispose();
				objCommand = null;
				objDataAdapter.Dispose();
				objDataAdapter = null;
			}
		}

		/// <summary>
		/// Execute sql query
		/// </summary>
		/// <returns></returns>
		public Boolean ExecuteStoredProcedure()
		{
			try
			{
				OpenConnection();
				if (objCommand.ExecuteNonQuery() != 0)
				{
					CloseConnection();
					return true;
				}
				else
				{
					CloseConnection();
                    return false;
				}
			}
			catch (System.Exception e)
			{
				throw new Exception(e.Message, e.InnerException);
			}
		}
	}
}